package ua.kharkov.khpi.tarasenko.bach.dao.mysql.category;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import ua.kharkov.khpi.tarasenko.bach.core.Constant;
import ua.kharkov.khpi.tarasenko.bach.core.db.DBUtils;
import ua.kharkov.khpi.tarasenko.bach.core.db.EShopDataStoreException;
import ua.kharkov.khpi.tarasenko.bach.dao.core.category.CategoryDAO;
import ua.kharkov.khpi.tarasenko.bach.dao.core.category.CategoryDTO;

/**
 * DAO for working with table with categories in Derby database.
 * 
 * @author oleksandr
 *
 */
public class MysqlCategoryDAO implements CategoryDAO {
	private static final Logger log = Logger.getLogger(MysqlCategoryDAO.class);

	// ===========================================================================
	// Table column's names
	// ===========================================================================
	private static final String COLUMN_ID = "CATEGORY_ID";
	private static final String COLUMN_NAME = "CATEGORY_NAME";

	// ===========================================================================
	// QUERIES
	// ===========================================================================
	private static final String QUERY_FIND_ALL_CATEGORIES = "SELECT * FROM " + Constant.TABLE_CATEGORY;

	private Connection conn;

	public MysqlCategoryDAO(Connection conn)
			throws EShopDataStoreException {
		if (conn == null) {
			throw new EShopDataStoreException("Connection is null!");
		}
		this.conn = conn;
	}

	@Override
	public ArrayList<CategoryDTO> findAllCategories() throws EShopDataStoreException {
		ArrayList<CategoryDTO> categories = new ArrayList<CategoryDTO>();
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ALL_CATEGORIES);
			rs = ps.executeQuery();
			CategoryDTO categoryDTO = null;
			while (rs.next()) {
				categoryDTO = extractCategory(rs);
				categories.add(categoryDTO);
			}
			if (log.isDebugEnabled()) {
				log.debug("Found " + categories.size() + " categories.");
			}
			if (log.isTraceEnabled()) {
				log.trace("Found catogires:\n" + categories);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for all items", e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return categories;
	}

	/**
	 * Extracts CategoryDTO from result set.
	 * 
	 * @param rs
	 *            - result set.
	 * @return extracted category.
	 * @throws SQLException
	 *             if error occurred.
	 */
	private CategoryDTO extractCategory(ResultSet rs) throws SQLException {
		CategoryDTO categoryDTO = new CategoryDTO();
		categoryDTO.setId(rs.getInt(COLUMN_ID));
		categoryDTO.setName(rs.getString(COLUMN_NAME));
		return categoryDTO;
	}

}
